﻿using CodeGenerator.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Dapper;
using Npgsql;

namespace CodeGenerator.Core
{
    public class PostgreSQLDatabase : IDatabase
    {
        /// <summary>
        /// 测试数据库链接
        /// </summary>
        /// <param name="server"></param>
        /// <param name="errMessage"></param>
        /// <returns></returns>
        public bool TestConnnect(ServerInfo server, out string errMessage)
        {
            string connectionString = Config.GetConnectionString(server);

            try
            {
                using (IDbConnection conn = new NpgsqlConnection(connectionString))
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    errMessage = string.Empty;
                    return true;
                }
            }
            catch (SqlException err)
            {
                errMessage = err.Message;
                return false;
            }
        }

        public IList<string> GetDatabaseList(ServerInfo server)
        {
            IList<string> dbList = new List<string>();
            if (!string.IsNullOrEmpty(server.DatabaseName))
            {
                dbList.Add(server.DatabaseName);
            }
            else
            {
                string strSql = "select datname as name from pg_database where datistemplate ='f'";

                using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server)))
                {
                    while (dr.Read())
                    {
                        dbList.Add(dr["name"].ToString());
                    }
                }
            }
            return dbList;
        }

        public IList<TableInfo> GetTableList(ServerInfo server, string dbName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" select a.relname , b.description from pg_class a 
                                left join (select * from pg_description where objsubid =0 ) b on a.oid = b.objoid
                                where a.relname in (select tablename from pg_tables where schemaname = 'public')
                                order by a.relname asc");

            using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server, dbName)))
            {
                IList<TableInfo> tbList = new List<TableInfo>();

                while (dr.Read())
                {
                    TableInfo table = new TableInfo();
                    table.Name = dr["relname"].ToString();
                    if (dr["description"] != null)
                        table.Comments = dr["description"].ToString();

                    tbList.Add(table);
                }
                return tbList;
            }
        }

        public List<ColumnInfo> GetColumnInfoList(ServerInfo server, string dbName, string tableName)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat(@"select ordinal_position as Id,
                                    column_name as ColumnName,
                                    udt_name as DataType,
                                    coalesce(character_maximum_length,numeric_precision,-1) as MaxLength,
                                    numeric_scale as Scale,
                                    case is_nullable when 'NO' then '' else '是' end as AllowDBNull,
                                    column_default as DefaultValue,
                                    case  when position('nextval' in column_default)>0 then '是' else '' end as IsIdentity, 
                                    case when b.pk_name is null then '' else '是' end as IsPrimaryKey,
                                    c.DeText as Comments
                                    from information_schema.columns 
                                    left join (
                                        select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint  
                                        inner join pg_class on pg_constraint.conrelid = pg_class.oid 
                                        inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and  pg_attr.attnum = pg_constraint.conkey[1] 
                                        inner join pg_type on pg_type.oid = pg_attr.atttypid
                                        where pg_class.relname = '{0}' and pg_constraint.contype='p' 
                                    ) b on b.colname = information_schema.columns.column_name
                                    left join (
                                        select attname,description as DeText from pg_class
                                        left join pg_attribute pg_attr on pg_attr.attrelid= pg_class.oid
                                        left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid=pg_attr.attnum
                                        where pg_attr.attnum>0 and pg_attr.attrelid=pg_class.oid and pg_class.relname='{0}'
                                    )c on c.attname = information_schema.columns.column_name
                                    where table_schema='public' and table_name='{0}' order by ordinal_position asc", tableName);

                using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server, dbName)))
                {
                    List<ColumnInfo> list = new List<ColumnInfo>();

                    while (dr.Read())
                    {
                        ColumnInfo columnInfo = new ColumnInfo();
                        columnInfo.Id = dr["Id"].ToString();
                        columnInfo.Name = dr["ColumnName"].ToString();
                        columnInfo.DataType = dr["DataType"].ToString();
                        columnInfo.AutoIncrement = dr["IsIdentity"].ToString();
                        columnInfo.MaxLength = Convert.ToInt32(dr["MaxLength"]);
                        // columnInfo.DecimalNum = Convert.ToInt32(dr["Scale"]);
                        columnInfo.DefaultValue = dr["DefaultValue"].ToString();
                        columnInfo.AllowDBNull = dr["AllowDBNull"].ToString();
                        columnInfo.IsPrimaryKey = dr["IsPrimaryKey"].ToString();
                        columnInfo.Comments = dr["Comments"].ToString();

                        columnInfo.NetType = this.GetNetType(columnInfo.DataType, columnInfo.AllowDBNull);
                        columnInfo.JavaType = this.GetJavaType(columnInfo.DataType);

                        columnInfo.IsNumeric = DataTypeHelper.IsNumeric(columnInfo.NetType.Alias);
                        columnInfo.IsDateTime = DataTypeHelper.IsDateTime(columnInfo.NetType.Alias);
                        columnInfo.IsBoolean = DataTypeHelper.IsBoolean(columnInfo.NetType.Alias);

                        columnInfo.UIControlType = DataTypeHelper.GetUIControlType(columnInfo);

                        list.Add(columnInfo);
                    }
                    return list;
                }

            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取表结构信息：", ex);
                return null;
            }
        }


        /// <summary>
        /// 获取表说明
        /// </summary>
        /// <param name="tableName">表明</param>
        /// <returns></returns>
        public string GetTableComments(ServerInfo server, string dbName, string tableName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(" select description from pg_description d INNER JOIN pg_class c on d.objoid=c.relfilenode  where objsubid=0 and relname='{0}'", tableName);

            string result = "";
            using (IDataReader dr = ExecuteReader(strSql.ToString(), Config.GetConnectionString(server)))
            {
                while (dr.Read())
                {
                    if (dr["description"] != null)
                        result = dr["description"].ToString();
                }
                return result;
            }
        }

        #region 类型转换

        /// <summary>
        /// Sql类型转C#类型
        /// </summary>
        /// <param name="typeName"></param>
        /// <param name="isNull"></param>
        /// <returns></returns>
        public NameElement GetNetType(string typeName, string isNull)
        {
            NameElement name = new NameElement();
            switch (typeName.Trim().ToLower())
            {
                case "bool":
                    name.Alias = "bool";
                    name.Name = "System.Boolean";
                    name.JdbcType = SqlDbType.Bit.ToString();
                    break;
                case "int4":
                    name.Alias = isNull == "是" ? "int?" : "int";
                    name.Name = isNull == "是" ? "System.Int32?" : "System.Int32";
                    name.JdbcType = SqlDbType.Int.ToString();
                    break;
                case "int2":
                    name.Alias = isNull == "是" ? "short?" : "short";
                    name.Name = isNull == "是" ? "System.Int16?" : "System.Int16";
                    name.JdbcType = SqlDbType.SmallInt.ToString();
                    break;
                case "int8":
                    name.Alias = "long";
                    name.Name = isNull == "是" ? "System.Int64?" : "System.Int64";
                    name.JdbcType = SqlDbType.BigInt.ToString();
                    break;
                case "float4":
                    name.Alias = isNull == "是" ? "float?" : "float";
                    name.Name = isNull == "是" ? "System.Single?" : "System.Single";
                    name.JdbcType = SqlDbType.Float.ToString();
                    break;
                case "float8":
                    name.Alias = isNull == "是" ? "double?" : "double";
                    name.Name = isNull == "是" ? "System.Double?" : "System.Double";
                    name.JdbcType = SqlDbType.Real.ToString();
                    break;
                case "numeric":
                    name.Alias = isNull == "是" ? "decimal?" : "decimal";
                    name.Name = isNull == "是" ? "System.Decimal?" : "System.Decimal";
                    name.JdbcType = SqlDbType.SmallMoney.ToString();
                    break;
                case "money":
                    name.Alias = isNull == "是" ? "decimal?" : "decimal";
                    name.Name = isNull == "是" ? "System.Decimal?" : "System.Decimal";
                    name.JdbcType = SqlDbType.Money.ToString();
                    break;
                case "text":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.Text.ToString();
                    break;
                case "varchar":
                    name.Alias = "string";
                    name.Name = "System.String";
                    name.JdbcType = SqlDbType.VarChar.ToString();
                    break;
                case "timestamp":
                    name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.JdbcType = SqlDbType.Date.ToString();
                    break;
                case "timestamptz":
                    name.Alias = isNull == "是" ? "DateTime?" : "DateTime";
                    name.Name = isNull == "是" ? "System.DateTime?" : "System.DateTime";
                    name.JdbcType = SqlDbType.DateTime.ToString();
                    break;
            }
            return name;
        }

        /// <summary>
        /// Sql类型转Java类型
        /// </summary>
        /// <param name="typeName"></param>
        /// <param name="isNull"></param>
        /// <returns></returns>
        public NameElement GetJavaType(string typeName)
        {
            NameElement name = new NameElement();
            switch (typeName.Trim().ToLower())
            {

                case "bool":
                    name.Alias = "Boolean";
                    name.Name = "java.lang.Boolean";
                    name.JdbcType = "BOOLEAN";
                    break;
                case "int4":
                    name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "INTEGER";
                    break;
                case "int2":
                    name.Alias = "Integer";
                    name.Name = "java.lang.Integer";
                    name.JdbcType = "SMALLINT";
                    break;
                case "int8":
                    name.Alias = "Long";
                    name.Name = "java.lang.Long";
                    name.JdbcType = "BIGINT";
                    break;
                case "float4":
                    name.Alias = "Double";
                    name.Name = "java.lang.Double";
                    name.JdbcType = "DOUBLE";
                    break;
                case "float8":
                    name.Alias = "Double";
                    name.Name = "java.lang.Double";
                    name.JdbcType = "DOUBLE";
                    break;
                case "numeric":
                    name.Alias = "BigDecimal";
                    name.Name = "java.math.BigDecimal";
                    name.JdbcType = "DECIMAL";
                    break;
                case "money":
                    name.Alias = "BigDecimal";
                    name.Name = "java.math.BigDecimal";
                    name.JdbcType = "DECIMAL";
                    break;
                case "text":
                    name.Name = "java.lang.String";
                    name.JdbcType = "VARCHAR";
                    break;
                case "varchar":
                    name.Alias = "String";
                    name.Name = "java.lang.String";
                    name.JdbcType = "VARCHAR";
                    break;
                case "timestamp":
                    name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "DATE";
                    break;
                case "timestamptz":
                    name.Alias = "Date";
                    name.Name = "java.util.Date";
                    name.JdbcType = "DATE";
                    break;
            }
            return name;
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 专供获取数据库用
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private IDataReader ExecuteReader(string strSQL, string connectionString)
        {
            NpgsqlConnection connection = new NpgsqlConnection(connectionString);
            IDbCommand cmd = new NpgsqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
        }

        public List<ColumnInfo> GetNHibernateColumnInfoList(ServerInfo server, string dbName, string tableName)
        {
            throw new NotImplementedException();
        }

        public NameElement GetNHibernateType(string typeName, string isNull, int length)
        {
            throw new NotImplementedException();
        }
        #endregion
    }
}
